package com.qf.index;

import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.NumberUtil;
import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import cn.hutool.json.JSONUtil;

import java.io.IOException;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;

public class Subject {

    public static void main(String[] args) throws SQLException {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        System.out.println(format.format(new Date()) + " 程序开始了~~~");

        String filePath = "F:\\2025年\\基于科室发表的论文.txt";
        String stemFuDanSubjectTopTable = "scholar_25_02.stem_fudan_subject_top";
        String pmArticleTable = "scholar_25_03.spider_pubmed_vip_extend";
        String ncbiJournalPdfTable = "dir.ncbijournalpdf_20250312_fullpath";
        String downPdfTable = "dir.ncbijournalpdf_20250312_fullpath_down";

        Set<String> nlmIdSet = insertTable(stemFuDanSubjectTopTable, filePath);
        System.out.println(format.format(new Date()) + " 将文本格式的文件插表，并返回nlmId号的集合，nlmIdSet.size()：" + nlmIdSet.size());

        Map<String, Set<Integer>> nlmIdAndPmIdSetMap = getNlmIdAndPmIdSetMap(pmArticleTable, "id", "med_nlm_id`,`pub_year`,`pmid", nlmIdSet);
        System.out.println(format.format(new Date()) + " 获取各nlmId所对应的近6年的pmId号集合，nlmIdAndPmIdSetMap.size()：" + nlmIdAndPmIdSetMap.size());

        Set<Integer> pmIdSet = getPmIdSet(stemFuDanSubjectTopTable, "id", "nlmid", nlmIdAndPmIdSetMap);
        System.out.println(format.format(new Date()) + " 更新各nlmId所需要下载的pmId号集合，并返回所有需要下的pmId号集合，pmIdSet.size()：" + pmIdSet.size());

        insertDownPdfTable(ncbiJournalPdfTable, "pid", "fullpath", pmIdSet, downPdfTable);
        System.out.println(format.format(new Date()) + " 将所有需要下载的pmId号插入一张新表。");


    }

    public static void insertDownPdfTable(String ncbiJournalPdfTable, String autoId, String field, Set<Integer> pmIdSet, String downPdfTable) throws SQLException {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Db.use().execute("truncate table " + downPdfTable);
        int onePageNum = 1000;
        int tableMax = Db.use().queryNumber("select max(" + autoId + ") from " + ncbiJournalPdfTable).intValue();
        if (tableMax > 0) {
            Collection<Integer> tableList = new ArrayList<>();
            NumberUtil.appendRange(0, tableMax + onePageNum, onePageNum, tableList);
            for (Integer startId : tableList) {
                List<Entity> tableData = Db.use().query("select `" + autoId + "`,`" + field + "` from " + ncbiJournalPdfTable + " where " + autoId + " between " + startId + " and " + (startId + onePageNum - 1));
//                System.out.println("\033[31;1m" + format.format(new Date()) + " 读表现在开始：" + startId + " 表名：" + ncbiJournalPdfTable + Utils.printMemoryStr() + "\033[0m");
                for (Entity entry : tableData) {
                    String fullPath = entry.getStr("fullpath");
                    Integer id = entry.getInt(autoId);
                    String fileId = Utils.getFileId(fullPath);
                    if (null != id && id > 0 && fileId.startsWith("PM") && fileId.length() > 2 && NumberUtil.isInteger(fileId.substring(2))) {
                        int pmId = Integer.parseInt(fileId.substring(2));
                        if (pmIdSet.contains(pmId)) {
                            try {
                                Db.use().insert(Entity.create(downPdfTable).set("fullpath", fullPath).set("pmId", pmId));
                                pmIdSet.remove(pmId);
                            } catch (SQLException e) {
                                e.printStackTrace();
                            }
                        }
                    }
                }
            }
        }
    }

    public static Set<Integer> getPmIdSet(String stemFuDanSubjectTopTable, String autoId, String field, Map<String, Set<Integer>> nlmIdAndPmIdSetMap) throws SQLException {
        int onePageNum = 1000;
        Set<Integer> pmIdSet = new HashSet<>();
        int tableMax = Db.use().queryNumber("select max(" + autoId + ") from " + stemFuDanSubjectTopTable).intValue();
        if (tableMax > 0) {
            Collection<Integer> tableList = new ArrayList<>();
            NumberUtil.appendRange(0, tableMax + onePageNum, onePageNum, tableList);
            for (Integer startId : tableList) {
                List<Entity> tableData = Db.use().query("select `" + autoId + "`,`" + field + "` from " + stemFuDanSubjectTopTable + " where " + autoId + " between " + startId + " and " + (startId + onePageNum - 1));
                for (Entity entry : tableData) {
                    String nlmId = entry.getStr("nlmid");
                    Integer id = entry.getInt(autoId);
                    if (null != nlmId && !nlmId.isEmpty() && nlmIdAndPmIdSetMap.containsKey(nlmId.toUpperCase())) {
                        Set<Integer> set = nlmIdAndPmIdSetMap.get(nlmId.toUpperCase());
                        pmIdSet.addAll(set);
                        try {
                            Db.use().update(Entity.create().set("pimd_list", JSONUtil.toJsonStr(set)).set("size", set.size()), Entity.create(stemFuDanSubjectTopTable).set(autoId, id));
                        } catch (SQLException e) {
                            e.printStackTrace();
                        }
                    }
                }
            }
        }
        return pmIdSet;
    }

    public static Map<String, Set<Integer>> getNlmIdAndPmIdSetMap(String pmArticleTable, String autoId, String field, Set<String> nlmIdSet) throws SQLException {
        int onePageNum = 1000;
        Map<String, Set<Integer>> nlmIdAndPmIdSetMap = new HashMap<>();
        int tableMax = Db.use().queryNumber("select max(" + autoId + ") from " + pmArticleTable).intValue();
        if (tableMax > 0) {
            Collection<Integer> tableList = new ArrayList<>();
            NumberUtil.appendRange(0, tableMax + onePageNum, onePageNum, tableList);
            for (Integer startId : tableList) {
                List<Entity> tableData = Db.use().query("select `" + autoId + "`,`" + field + "` from " + pmArticleTable + " where " + autoId + " between " + startId + " and " + (startId + onePageNum - 1));
                for (Entity entry : tableData) {
                    String medNlmId = Utils.getStrField(entry, "med_nlm_id").toUpperCase();
                    String year = Utils.getStrField(entry, "pub_year");
                    int pmId = Utils.getIntField(entry, "pmid");
                    if (!medNlmId.isEmpty() && nlmIdSet.contains(medNlmId) && ("2019".equals(year) || "2020".equals(year) || "2021".equals(year) || "2022".equals(year) || "2023".equals(year) || "2024".equals(year) || "2025".equals(year)) && pmId > 0) {
                        Utils.putStrSetInt(medNlmId, pmId, nlmIdAndPmIdSetMap);
                    }
                }
            }
        }
        return nlmIdAndPmIdSetMap;
    }

    public static Set<String> insertTable(String stemFuDanSubjectTopTable, String filePath) throws SQLException {
        Db.use().execute("truncate table " + stemFuDanSubjectTopTable);
        List<String> list = FileUtil.readLines(filePath, "utf-8");
        Set<String> nlmIdSet = new HashSet<>();
        for (String s : list) {
            String[] split = s.split("\\|");
            if (split.length == 7 && NumberUtil.isInteger(split[3])) {
                try {
                    Db.use().insert(Entity.create(stemFuDanSubjectTopTable)
                            .set("top_type", split[0])
                            .set("stem_fudan", split[1])
                            .set("subject", split[2])
                            .set("jid", Integer.parseInt(split[3]))
                            .set("nlmid", split[5])
                            .set("abbr", split[4]));
                    nlmIdSet.add(split[5].toUpperCase().trim());
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return nlmIdSet;
    }


}
